﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Windows.Forms;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SQLite;
using System.IO;

namespace gradeManager
{
    //创建数据库连接的工具类
    class LinkSqLiteDB
    {
        SQLiteConnection  sqlCnn ; //创建数据库连接对象
       SQLiteCommand sqlCmd ;       //创建执行sql的对象

        public Boolean DBtag = false;
        //创建数据库连接
        public Boolean DBconn()
        {
            sqlCnn = new SQLiteConnection();
            sqlCmd = new SQLiteCommand();
            //sqlCnn.ConnectionString = "server = 'localhost'; uid = 'root'; pwd = 'tian951028'; database = 'grade';";//连接字符串
            sqlCnn.ConnectionString = "Data Source=../../../../mfc.db";
            sqlCmd.Connection = sqlCnn;
            try
            {
                sqlCnn.Open();
                DBtag = true;
                if (DBtag)
                {
                    //Console.WriteLine("数据库连接成功");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误！");

            }
            finally
            { 
       
            }
            return DBtag;
        }

        //执行查询的sql
        public Boolean search(String str)
        {
            sqlCmd.CommandText = str;
            Boolean tag = false;
            SQLiteDataReader rec = sqlCmd.ExecuteReader();//创建结果集
            while (rec.Read())
            {
                tag = true;
                // Console.WriteLine("查询到数据");
            }
            return tag;
        }

        //执行增删改的sql
        public Boolean UpdataDeletAdd(string str)
        {
            sqlCmd.CommandText = str;
            return sqlCmd.ExecuteNonQuery() > 0;
        }
        //关闭数据库连接
        public void DBclose()
        {
            sqlCnn.Close();
            sqlCnn.Dispose();
            //Console.WriteLine("数据库连接关闭");
            //Console.ReadKey();
        }
        //获取登录时所需要的数据login_info
        public Boolean GetLoginData(string str)
        {
            sqlCmd.CommandText = str;
            SQLiteDataReader rec = sqlCmd.ExecuteReader();//创建结果集
            while (rec.Read())  //查询到数据结果
            {
                LoginInfo.stuid = rec[0].ToString();  //将用户名存放到数据实体类
                LoginInfo.password = rec.GetString(1);  //将密码存放在数据实体类
                LoginInfo.stuname = rec.GetString(2);//姓名
                Console.WriteLine("查询到的信息：学号{0}，密码{1}，姓名{2}", LoginInfo.stuid,LoginInfo.password,LoginInfo.stuname);
                rec.Close();
                return true;    //查询到用户数据
            }
            return false;
        }

        public DataSet GetScoreDs(string sql)
        {
            DataSet ds = new DataSet();
            //SQLiteConnection mySqlConnection = new SQLiteConnection("Data Source=" + Application.ExecutablePath.Substring(0, Application.ExecutablePath.IndexOf(@"gradeManager\")) + "mfc.db");
            SQLiteConnection mySqlConnection = sqlCnn;
            SQLiteCommand mySqlCommand = new SQLiteCommand(sql, mySqlConnection);
          SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(mySqlCommand);
            dataAdapter.Fill(ds);
            return ds;
        }

        //获取成绩表中的数据
        public Boolean GetScoreData(string str)
        {
            sqlCmd.CommandText = str;
            Boolean tag = false;
            SQLiteDataReader rec = sqlCmd.ExecuteReader();//创建结果集
            int aa = 0;
            //初始化数据
            ScoreInfo.id.Clear();
            ScoreInfo.semester.Clear();
            ScoreInfo.stuid.Clear();
            ScoreInfo.teacher.Clear();
            ScoreInfo.numofcourse.Clear();
            ScoreInfo.nameofcourse.Clear();
            ScoreInfo.credit.Clear();
            ScoreInfo.grade.Clear();
            ScoreInfo.stuname.Clear();

            while (rec.Read())  //查询到数据结果
            {
                ScoreInfo.id.Add(rec.GetString(0));     //将查询到的id存储到数据实体类
                ScoreInfo.stuid.Add(rec.GetString(1));//将查询到的学号存储到数据实体类
                ScoreInfo.semester.Add(rec.GetString(2));//将查询到的学期归属存储到数据实体类
                ScoreInfo.numofcourse.Add(rec.GetString(3));//将查询到的课头号存储到数据实体类
                ScoreInfo.nameofcourse.Add(rec.GetString(4));//将查询到的课程名存储到数据实体类
                ScoreInfo.teacher.Add(rec.GetString(5));//教师
                ScoreInfo.credit.Add(rec.GetString(6));
                ScoreInfo.grade.Add(rec.GetString(7));
                ScoreInfo.stuname.Add(rec.GetString(8));

                tag = true;    //查询到用户数据
                aa++;
            }
            //Console.WriteLine("获取到"+aa+"条数据");
            return tag;
        }

        //获取用户信息表中的数据
        public Boolean GetUserInfoData(string str)
        {
            sqlCmd.CommandText = str;
            Boolean tag = false;
            SQLiteDataReader rec = sqlCmd.ExecuteReader();//创建结果集
            int aa = 0;
            //初始化数据
            LoginInfo.idList.Clear();
            LoginInfo.passwordList.Clear();
            LoginInfo.stunameList.Clear();
            while (rec.Read())  //查询到数据结果
            {
                LoginInfo.idList.Add(rec.GetString(0));     //存储到数据实体类
                LoginInfo.passwordList.Add(rec.GetString(1));//将查询到的密码存储到数据实体类
                LoginInfo.stunameList.Add(rec.GetString(2));//将查询到的身份存储到数据实体类

                tag = true;    //查询到用户数据
                aa++;
            }
            //Console.WriteLine("获取到" + aa + "条数据");
            return tag;
        }
    }
}


